package edu.uoc.tdp.pac4.database;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.swing.JOptionPane;

import edu.uoc.tdp.pac4.beans.Course;
import edu.uoc.tdp.pac4.client.stats.StatResultData;
import edu.uoc.tdp.pac4.common.util.LanguageUtils;

public class StatsManager {

	Connection con = null;
	private final String driver = "org.postgresql.Driver";
	PreparedStatement pst = null;
	DateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
	boolean connected = false;

	public StatsManager() {
		super();
		//JUST LOCAL:
		//con = conectaBD();
		//ENTREGA:
		try {
			con = DiskManager.getInstance().getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<StatResultData> getStudentStatistic() {
		List<StatResultData> lista = new ArrayList<StatResultData>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			String query = "select * from usuari;";
			rs = st.executeQuery(query);
			while (rs.next()) {
				StatResultData res = new StatResultData();
				res.setNomAlumne(rs.getString("nom"));
				res.setCognomsAlumne(rs.getString("cognoms"));
				res.setNomCurs("DummyCurs");
				res.setDataInici(new Date(0));
				res.setDataFi(new Date(0));
				res.setAssistReq(50);
				res.setAssistActual(40);
				res.setAssistDiff((40 - 50));
				lista.add(res);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return lista;
	}

	private Connection conectaBD() {
		Connection connect = null;
		try {
			Class.forName(driver);
			Properties prop = new Properties();
			// prop.load(ClassLoader.getSystemClassLoader().getResourceAsStream(propertiesFile));

			String path = getClass().getProtectionDomain().getCodeSource()
					.getLocation().toString().substring(6);
			path = "/" + path.subSequence(0, path.length() - 4).toString();
			java.io.FileInputStream fis = new java.io.FileInputStream(
					new java.io.File(path + "/properties/conf.properties"));
			prop.load(fis);

			String url = prop.getProperty("url");
			String usuario = prop.getProperty("username");
			String clave = prop.getProperty("password");
			connect = DriverManager.getConnection(url, usuario, clave);
			// System.out.println("ESTAMOS CONECTADOS A POSTGRESQL!");
		} catch (ClassNotFoundException exc) {
			exc.printStackTrace();
		} catch (FileNotFoundException exc) {
			exc.printStackTrace();
		} catch (IOException exc) {
			exc.printStackTrace();
		} catch (SQLException exc) {
			exc.printStackTrace();
		} catch (Exception exc) {
			JOptionPane.showMessageDialog(null,
					LanguageUtils.getMessage("client.error.message.generic"),
					LanguageUtils.getMessage("client.error.title"),
					JOptionPane.ERROR_MESSAGE);
			//return false;
		}
		return connect;
	}

	public List<Course> getAllCourses() {
		Statement st = null;
		ResultSet rs = null;
		List<Course> courses = new ArrayList<Course>();
		try {
			st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			String query = "select * from curs";
			rs = st.executeQuery(query);

			while (rs.next()) {
				Course course = new Course();
				course.setCourseCode(rs.getInt(1));
				course.setCourseName(rs.getString(2));
				courses.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return courses;

	}

	//STUDENT:
	public List<StatResultData> getStudentStatistic(java.util.Date date, Integer typeCourse, String dniAlumne) {
		List<StatResultData> lista = new ArrayList<StatResultData>();
		//Codes: All=3, Finished=2, Not finished=1
		PreparedStatement query = null;
		java.util.Date toDa = new java.util.Date();
		java.sql.Date todayDt = new java.sql.Date(toDa.getTime());
		ResultSet result = null;
		try {
			if(date !=null){
				java.sql.Date dt = new java.sql.Date(date.getTime());
					String qr = "";
					switch (typeCourse) {
					case 1: //No acabats
						qr = "select a.totalasisten, u.*, g.*, c.minassistencia, c.nom as nomcurs "
							+ " from matriculas m "
							+ " left join usuari u "
							+ " on m.usuarioid = u.nif "
							+ " left join grupo g "
							+ " on g.grupoid = m.grupoid "
							+ " left join curs c "
							+ " on g.idcurso = c.id " 
							+ " left join asistencia a on a.grupoid = g.grupoid "
							+ " where g.fechainicio >= ? "
							+ " and g.fechafin <= ? "
							+ " and u.nif = ? ";
						query = con.prepareStatement(qr);
						query.setDate(1, dt);
						query.setDate(2, todayDt);
						query.setString(3, dniAlumne);
						result = query.executeQuery();
						break;
					case 2:
						qr = "select a.totalasisten, u.*, g.*, c.minassistencia, c.nom as nomcurs "
								+ " from matriculas m "
								+ " left join usuari u "
								+ " on m.usuarioid = u.nif "
								+ " left join grupo g "
								+ " on g.grupoid = m.grupoid "
								+ " left join curs c "
								+ " on g.idcurso = c.id "
								+ " left join asistencia a on a.grupoid = g.grupoid "
								+ " where g.fechainicio >= ? "
								+ " and g.fechafin >= ? "
								+ " and u.nif = ? ";
						query = con.prepareStatement(qr);
						query.setDate(1, dt);
						query.setDate(2, todayDt);
						query.setString(3, dniAlumne);
						result = query.executeQuery();
						break;
					case 3:
						qr = "select a.totalasisten, u.*, g.*, c.minassistencia, c.nom as nomcurs "
								+ " from matriculas m "
								+ " left join usuari u "
								+ " on m.usuarioid = u.nif "
								+ " left join grupo g "
								+ " on g.grupoid = m.grupoid "
								+ " left join curs c "
								+ " on g.idcurso = c.id "
								+ " left join asistencia a on a.grupoid = g.grupoid "
								+ " where g.fechainicio <= ? "
								+ " and u.nif = ? ";
						query = con.prepareStatement(qr);
						query.setDate(1, dt);
						query.setString(2, dniAlumne);
						result = query.executeQuery();
						break;
					}
			}else{
				//Without
				String qr = "";
				if(typeCourse != null && !"".equals(typeCourse)){
					switch (typeCourse) {
					case 1:
						qr = "select a.totalasisten, u.*, g.*, c.minassistencia, c.nom as nomcurs "
							+ " from matriculas m "
							+ " left join usuari u "
							+ " on m.usuarioid = u.nif "
							+ " left join grupo g "
							+ " on g.grupoid = m.grupoid "
							+ " left join curs c "
							+ " on g.idcurso = c.id "
							+ " left join asistencia a on a.grupoid = g.grupoid "
							+ " where g.fechafin <= ? "
							+ " and u.nif = ? ";
						query = con.prepareStatement(qr);
						query.setDate(1, todayDt);
						query.setString(2, dniAlumne);
						result = query.executeQuery();
						break;
					case 2:
						qr = "select a.totalasisten, u.*, g.*, c.minassistencia, c.nom as nomcurs "
								+ " from matriculas m "
								+ " left join usuari u "
								+ " on m.usuarioid = u.nif "
								+ " left join grupo g "
								+ " on g.grupoid = m.grupoid "
								+ " left join curs c "
								+ " on g.idcurso = c.id "
								+ " left join asistencia a on a.grupoid = g.grupoid "
								+ " where g.fechafin >= ? "
								+ " and u.nif = ? ";
						query = con.prepareStatement(qr);
						query.setDate(1, todayDt);
						query.setString(2, dniAlumne);
						result = query.executeQuery();
						break;
					case 3:
						qr = "select  a.totalasisten,u.*, g.*, c.minassistencia, c.nom as nomcurs "
								+ " from matriculas m "
								+ " left join usuari u "
								+ " on m.usuarioid = u.nif "
								+ " left join grupo g "
								+ " on g.grupoid = m.grupoid "
								+ " left join curs c "
								+ " on g.idcurso = c.id "
								+ " left join asistencia a on a.grupoid = g.grupoid "
								+ " where u.nif = ? ";
						query = con.prepareStatement(qr);
						query.setString(1, dniAlumne);
						result = query.executeQuery();
						break;
					}
				}
			}
				
			if(result != null){
				StatResultData data = null;
				while (result.next()) {
					data = new StatResultData();
					data.setNomCurs(result.getString("nomcurs"));
					data.setAssistReq(result.getInt("minassistencia"));
					data.setDataInici(result.getDate("fechainicio"));
					data.setDataFi(result.getDate("fechafin"));
					data.setAssistReq(result.getInt("minassistencia"));
					data.setNomAlumne(result.getString("nom"));
					data.setCognomsAlumne(result.getString("cognoms"));
					data.setAssistActual(result.getInt("totalasisten"));
					lista.add(data);
				}
			}
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					result.close();
					query.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lista;
	}

	//Techer
	public List<StatResultData> getTeacherStat1(java.util.Date date, String nameCourse,String dniProf) {
		List<StatResultData> lista = new ArrayList<StatResultData>();
		PreparedStatement query = null;
		ResultSet result = null;
		try {
			if(date !=null){
				java.sql.Date dt = new java.sql.Date(date.getTime());
				if(nameCourse != null && !"".equals(nameCourse)){
					String qr = "select * from curs c"
					+" left join grupo g"
					+" on g.idcurso = c.id"
					+ " left join asistencia a on a.grupoid = g.grupoid "
					+" and g.fechainicio >= ? "
					+" where g.idprofesor = ? "
					+" and c.nom = ? ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					query.setString(2, dniProf);
					query.setString(3, nameCourse);
					result = query.executeQuery();
			
				}else{
					String qr = "select * from curs c "
					+" left join grupo g "
					+" on g.idcurso = c.id "
					+ " left join asistencia a on a.grupoid = g.grupoid "
					+" and g.fechainicio >= ? " 
					+" where g.idprofesor = ? ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					query.setString(2, dniProf);
					result = query.executeQuery();
				}
				
			}else{
				//Without
				if(nameCourse != null && !"".equals(nameCourse)){
					String qr = "select * from curs c "
					+" left join grupo g "
					+" on g.idcurso = c.id "
					+ " left join asistencia a on a.grupoid = g.grupoid "
					+" where g.idprofesor = ? "
					+" and c.nom = ? ";
					query = con.prepareStatement(qr);
					query.setString(1, dniProf);
					query.setString(2, nameCourse);
					result = query.executeQuery();
	
				}else{
					String qr = "select * from curs c "
					+" left join grupo g "
					+" on g.idcurso = c.id "
					+ " left join asistencia a on a.grupoid = g.grupoid "
					+" where g.idprofesor = ? ";
					query = con.prepareStatement(qr);
					query.setString(1, dniProf);
					result = query.executeQuery();
				}
			}
				
			if(result != null){
				StatResultData data = null;
				while (result.next()) {
					data = new StatResultData();
					data.setNomCurs(result.getString("nom"));
					data.setAssistReq(result.getInt("minassistencia"));
					data.setDataInici(result.getDate("fechainicio"));
					data.setDataFi(result.getDate("fechafin"));
					data.setAssistReq(result.getInt("minassistencia"));
					data.setAssistActual(result.getInt("totalasisten"));
					lista.add(data);
				}
			}
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					result.close();
					query.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lista;
	}
	
	public List<StatResultData> getTeacherStat2(java.util.Date date, String nameCourse, String dniProf) {
		List<StatResultData> lista = new ArrayList<StatResultData>();
		PreparedStatement query = null;
		ResultSet result = null;
		try {
			if(date !=null){
				java.sql.Date dt = new java.sql.Date(date.getTime());
				if(nameCourse != null && !"".equals(nameCourse)){
					String qr = "select a.totalasisten, u.*, m.*, g.*, c.minassistencia, c.nom as nomcurs  from usuari u "
						+ " left join matriculas m on u.nif = m.usuarioid "
						+ " left join grupo g on g.grupoid = m.grupoid and g.fechainicio >= ? "
						+ " left join curs c on c.id = g.idcurso "
						+ " left join asistencia a on a.grupoid = g.grupoid "
						+ " where rolusuari = 'Alumne' "
						+ " and g.idprofesor = ? "
						+ " and c.nom = ? ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					query.setString(2, dniProf);
					query.setString(3, nameCourse);
					result = query.executeQuery();
			
				}else{
					String qr = "select a.totalasisten, u.*, m.*, g.*, c.minassistencia, c.nom as nomcurs  from usuari u "
						+ " left join matriculas m on u.nif = m.usuarioid "
						+ " left join grupo g on g.grupoid = m.grupoid and g.fechainicio >= ? "
						+ " left join curs c on c.id = g.idcurso "
						+ " left join asistencia a on a.grupoid = g.grupoid "
						+ " where rolusuari = 'Alumne' "
						+ " and g.idprofesor = ? ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					query.setString(2, dniProf);
					result = query.executeQuery();
				}
				
			}else{
				//Without
				if(nameCourse != null && !"".equals(nameCourse)){
					String qr = "select a.totalasisten, u.*, m.*, g.*, c.minassistencia, c.nom as nomcurs  from usuari u "
						+ " left join matriculas m on u.nif = m.usuarioid "
						+ " left join grupo g on g.grupoid = m.grupoid "
						+ " left join curs c on c.id = g.idcurso "
						+ " left join asistencia a on a.grupoid = g.grupoid "
						+ " where rolusuari = 'Alumne' "
						+ " and g.idprofesor = ? "
						+ " and c.nom = ? ";
					query = con.prepareStatement(qr);
					query.setString(1, dniProf);
					query.setString(2, nameCourse);
					result = query.executeQuery();
	
				}else{
					String qr = "select a.totalasisten, u.*, m.*, g.*, c.minassistencia, c.nom as nomcurs  from usuari u "
						+ " left join matriculas m on u.nif = m.usuarioid "
						+ " left join grupo g on g.grupoid = m.grupoid "
						+ " left join curs c on c.id = g.idcurso "
						+ " left join asistencia a on a.grupoid = g.grupoid "
						+ " where rolusuari = 'Alumne' "
						+ " and g.idprofesor = ? ";
					query = con.prepareStatement(qr);
					query.setString(1, dniProf);
					result = query.executeQuery();
				}
			}
				
			if(result != null){
				StatResultData data = null;
				while (result.next()) {
					data = new StatResultData();
					data.setNomCurs(result.getString("nomcurs"));
					data.setAssistReq(result.getInt("minassistencia"));
					data.setDataInici(result.getDate("fechainicio"));
					data.setDataFi(result.getDate("fechafin"));
					data.setAssistReq(result.getInt("minassistencia"));
					data.setNomAlumne(result.getString("nom"));
					data.setCognomsAlumne(result.getString("cognoms"));
					data.setAssistActual(result.getInt("totalasisten"));
					lista.add(data);
				}
			}
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					result.close();
					query.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lista;
	}

	
	//Staff
	public List<StatResultData> getStaffStat1(java.util.Date date, String nameCourse) {
		List<StatResultData> lista = new ArrayList<StatResultData>();
		PreparedStatement query = null;
		ResultSet result = null;
		try {
			if(date !=null){
				java.sql.Date dt = new java.sql.Date(date.getTime());
				String qr = "select u.nom, u.cognoms, "
						+ " ((select sum(totalasisten) from asistencia where grupoid in  "
						+ " (select g1.grupoid from grupo g1 where g1.idprofesor = u.nif and g1.fechainicio >= ? )) /  "
						+ " (select count(*) from grupo g where g.idprofesor = u.nif  and g.fechainicio >= ? )) as mitjana "
						+ " from usuari u "
						+ " where u.rolusuari = 'Professor' ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					query.setDate(2, dt);
					result = query.executeQuery();
				
			}else{
				String qr = "select u.nom, u.cognoms, "
						+ " ((select sum(totalasisten) from asistencia where grupoid in  "
						+ " (select g1.grupoid from grupo g1 where g1.idprofesor = u.nif)) /  "
						+ " (select count(*) from grupo g where g.idprofesor = u.nif )) as mitjana "
						+ " from usuari u "
						+ " where u.rolusuari = 'Professor' ";
					query = con.prepareStatement(qr);
					result = query.executeQuery();
			}
				
			if(result != null){
				StatResultData data = null;
				while (result.next()) {
					data = new StatResultData();
					String nomCompletProf = result.getString("nom");
					nomCompletProf = nomCompletProf + " " + result.getString("cognoms");
					data.setNomProfessor(nomCompletProf);
					data.setAssitMitjanaCursos(result.getFloat("mitjana"));
					
					lista.add(data);
				}
			}
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					result.close();
					query.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lista;
	}
	
	public List<StatResultData> getStaffStat2(java.util.Date date, String nameCourse) {
		List<StatResultData> lista = new ArrayList<StatResultData>();
		PreparedStatement query = null;
		ResultSet result = null;
		try {
			if(date !=null){
				java.sql.Date dt = new java.sql.Date(date.getTime());
				if(nameCourse != null && !"".equals(nameCourse)){
					String qr = "select u.nom, u.cognoms, c.nom as nomcurs, (a.totalasisten - c.minassistencia) as assist from usuari u"
							+ " left join grupo g on g.idprofesor = u.nif and g.fechainicio >= ? "
							+ " left join curs c on c.id = g.idcurso "
							+ " left join asistencia a on a.grupoid = g.grupoid "
							+ " where u.rolusuari = 'Professor' "
							+ " and c.nom = ? ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					query.setString(2, nameCourse);
					result = query.executeQuery();
			
				}else{
					String qr = "select u.nom, u.cognoms, c.nom as nomcurs, (a.totalasisten - c.minassistencia) as assist from usuari u"
							+ " left join grupo g on g.idprofesor = u.nif and g.fechainicio >= ? "
							+ " left join curs c on c.id = g.idcurso "
							+ " left join asistencia a on a.grupoid = g.grupoid "
							+ " where u.rolusuari = 'Professor' ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					result = query.executeQuery();
				}
				
			}else{
				//Without
				if(nameCourse != null && !"".equals(nameCourse)){
					String qr = "select u.nom, u.cognoms, c.nom as nomcurs, (a.totalasisten - c.minassistencia) as assist from usuari u"
							+ " left join grupo g on g.idprofesor = u.nif "
							+ " left join curs c on c.id = g.idcurso "
							+ " left join asistencia a on a.grupoid = g.grupoid "
							+ " where u.rolusuari = 'Professor' "
							+ " and c.nom = ? ";
					query = con.prepareStatement(qr);
					query.setString(1, nameCourse);
					result = query.executeQuery();
	
				}else{
					String qr = "select u.nom, u.cognoms, c.nom as nomcurs, (a.totalasisten - c.minassistencia) as assist from usuari u "
							+ " left join grupo g on g.idprofesor = u.nif "
							+ " left join curs c on c.id = g.idcurso "
							+ " left join asistencia a on a.grupoid = g.grupoid "
							+ " where u.rolusuari = 'Professor' ";
					query = con.prepareStatement(qr);
					result = query.executeQuery();
				}
			}
				
			if(result != null){
				StatResultData data = null;
				while (result.next()) {
					data = new StatResultData();
					data.setNomCurs(result.getString("nomcurs"));
					String nomCompletProf = result.getString("nom");
					nomCompletProf = nomCompletProf + " " + result.getString("cognoms");
					data.setNomProfessor(nomCompletProf);
					data.setAssitMitjanaCursos(result.getFloat("assist"));
					
					lista.add(data);
				}
			}
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					result.close();
					query.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lista;
	}
	
	public List<StatResultData> getStaffStat3(java.util.Date date, String dniAlumne) {
		/*
select u.nom, u.cognoms, u.nif, g.nombre as nomgrup, c.nom as nomcurs from asistencia a 
left join grupo g on g.grupoid = a.grupoid
left join asistenciaalumno aalum on aalum.asistid = a.asistid
inner join usuari u on u.nif = aalum.alumnoid
left join curs c on c.id = g.idcurso;
		 */
		List<StatResultData> lista = new ArrayList<StatResultData>();
		PreparedStatement query = null;
		ResultSet result = null;
		try {
			if(date !=null){
				java.sql.Date dt = new java.sql.Date(date.getTime());
				if(dniAlumne != null && !"".equals(dniAlumne)){
					String qr = "select u.nom, u.cognoms, u.nif, g.nombre as nomgrup, c.nom as nomcurs from asistencia a "
							+ " inner join grupo g on g.grupoid = a.grupoid and g.fechainicio >= ?  "
							+ " left join asistenciaalumno aalum on aalum.asistid = a.asistid "
							+ " inner join usuari u on u.nif = aalum.alumnoid "
							+ " left join curs c on c.id = g.idcurso "
							+ " where u.nif = ? ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					query.setString(2, dniAlumne);
					result = query.executeQuery();
			
				}else{
					String qr = "select u.nom, u.cognoms, u.nif, g.nombre as nomgrup, c.nom as nomcurs from asistencia a "
							+ " inner join grupo g on g.grupoid = a.grupoid and g.fechainicio >= ?"
							+ " left join asistenciaalumno aalum on aalum.asistid = a.asistid "
							+ " inner join usuari u on u.nif = aalum.alumnoid "
							+ " left join curs c on c.id = g.idcurso ";
					query = con.prepareStatement(qr);
					query.setDate(1, dt);
					result = query.executeQuery();
				}
				
			}else{
				//Without
				if(dniAlumne != null && !"".equals(dniAlumne)){
					String qr = "select u.nom, u.cognoms, u.nif, g.nombre as nomgrup, c.nom as nomcurs from asistencia a "
							+ " inner join grupo g on g.grupoid = a.grupoid "
							+ " left join asistenciaalumno aalum on aalum.asistid = a.asistid "
							+ " inner join usuari u on u.nif = aalum.alumnoid "
							+ " left join curs c on c.id = g.idcurso "
							+ " where u.nif = ? ";
					query = con.prepareStatement(qr);
					query.setString(1, dniAlumne);
					result = query.executeQuery();
	
				}else{
					String qr = "select u.nom, u.cognoms, u.nif, g.nombre as nomgrup, c.nom as nomcurs from asistencia a "
							+ " inner join grupo g on g.grupoid = a.grupoid "
							+ " left join asistenciaalumno aalum on aalum.asistid = a.asistid "
							+ " inner join usuari u on u.nif = aalum.alumnoid "
							+ " left join curs c on c.id = g.idcurso ";
					query = con.prepareStatement(qr);
					result = query.executeQuery();
				}
			}
				
			if(result != null){
				StatResultData data = null;
				while (result.next()) {
					data = new StatResultData();
					data.setNomCurs(result.getString("nomcurs"));
					data.setNomAlumne(result.getString("nom"));
					data.setCognomsAlumne(result.getString("cognoms"));
					data.setDniAlumne(result.getString("nif"));
					data.setNomGrup(result.getString("nomgrup"));
					
					lista.add(data);
				}
			}
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					result.close();
					query.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lista;
	}
	
	public List<StatResultData> getStaffStat4(java.util.Date dateIni, java.util.Date dateEnd) {
		return null;
	}
	public List<StatResultData> getStaffStat5(String nameCourse) {
/**
 select g.nombre, (select ( sum(totalasisten) - sum(totalnoasiten) ) 
from asistencia where grupoid = g.grupoid ) as ra  from grupo g;
 */
		List<StatResultData> lista = new ArrayList<StatResultData>();
		PreparedStatement query = null;
		ResultSet result = null;
		try {
			if(nameCourse != null && !"".equals(nameCourse)){
					String qr = "select g.nombre, (select ( sum(totalasisten) - sum(totalnoasiten) ) "
							+ " from asistencia where grupoid = g.grupoid ) as ratio from grupo g "
							+ " where g.nombre = ? ";
					query = con.prepareStatement(qr);
					query.setString(1, nameCourse);
					result = query.executeQuery();
			}else{
				String qr = "select g.nombre, (select ( sum(totalasisten) - sum(totalnoasiten) ) "
						+ " from asistencia where grupoid = g.grupoid ) as ratio from grupo g ";
				query = con.prepareStatement(qr);
				result = query.executeQuery();
			}
			
			if(result != null){
				StatResultData data = null;
				while (result.next()) {
					data = new StatResultData();
					data.setNomCurs(result.getString("nombre"));
					data.setAssitMitjanaCursos(result.getFloat("ratio"));
					lista.add(data);
				}
			}
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					result.close();
					query.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lista;
	}
	
}
